﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Net;
using System.Net.Sockets;
namespace LHWebApp
{

    /// <summary>
    ///Services 的摘要说明
    /// </summary>
    public class ServicesToilet
    {
        public static DataSet GetMenuAttendanceInfoDay(string clientNo, string date, string status)//菜单 考勤管理-考勤记录
        {
            //EXEC GetWorkerAttendenceNew '2020-03-25','0000004',''
            String url = string.Format("EXEC GetWorkerAttendenceNew '{0}','{1}','{2}'", date, clientNo, status);
            return GetData(url);
        }
        public static DataSet GetMenuAttendanceInfoMonth(string clientNo, string date)//菜单 考勤管理-考勤记录
        {
            //exec GetWorkerToiletAttendenceNewMonth '2020-03-25','00000'
            String url = string.Format("EXEC GetWorkerToiletAttendenceNewMonth '{0}','{1}'", date, clientNo);
            return GetData(url);
        }
        public static DataSet GetMenuToiletWorkInfo(string clientNo, string date, string name)//菜单 考勤管理-公厕作业
        {
            String url;
            if (name == "") {
                //select (select NAME from WORKER_TOILET where vid =b.vid),b.ORIENTATION_TIME,(select NAME from PLACE_TOILET where id =b.id),[SEX] from WORKER_TOILET_BLUETOOTH b where b.ORIENTATION_TIME between '2020-03-25' and '2020-03-25 23:59:59' and b.id is not null order by b.ORIENTATION_TIME desc
                url = string.Format("select (select NAME from WORKER_TOILET where vid =b.vid),b.ORIENTATION_TIME,(select NAME from PLACE_TOILET where id =b.id),[SEX] from WORKER_TOILET_BLUETOOTH b where b.ORIENTATION_TIME between '{0}' and '{0} 23:59:59' and b.id is not null order by b.ORIENTATION_TIME desc", date);
            } else {
                //select (select NAME from WORKER_TOILET where vid =b.vid),b.ORIENTATION_TIME,(select NAME from PLACE_TOILET where id =b.id),[SEX] from WORKER_TOILET_BLUETOOTH b where b.ORIENTATION_TIME between '2020-03-25' and '2020-03-25 23:59:59' and b.vid=(select vid from WORKER_TOILET where NAME ='全部') and b.id is not null order by b.ORIENTATION_TIME desc
                url = string.Format("select (select NAME from WORKER_TOILET where vid =b.vid),b.ORIENTATION_TIME,(select NAME from PLACE_TOILET where id =b.id),[SEX] from WORKER_TOILET_BLUETOOTH b where b.ORIENTATION_TIME between '{0}' and '{0} 23:59:59' and b.vid=(select vid from WORKER_TOILET where NAME ='{1}') and b.id is not null order by b.ORIENTATION_TIME desc", date, name);
            }
            return GetData(url);
        }

        public static DataSet GetMenuJobFeedback(string clientNo, string dateStrat, string dateEnd, string WCName)//菜单 作业反馈
        {
            //Select * from WORKER_DISPOSE where LEFT(CLIENT_NO,LEN('0000004'))=0000004
            String url = string.Format("Select * from [WORKER_DISPOSE] where LEFT(CLIENT_NO,LEN('{0}'))={1} AND ORIENTATION_TIME between '{2}' AND '{3}' and ROADNAME like '%{4}%'", clientNo, clientNo, dateStrat, dateEnd, WCName);
            return GetData(url);
        }

        public static DataSet GetMenuTemporaryMission(string clientNo, string date, string name, string status)//菜单 临时任务
        {
            //EXEC GetWorkerToiletMissionForCs '0000004','2020-03-25','公厕维保队',''
            String url = string.Format("exec GetWorkerToiletMissionForCs '{0}','{1}','{2}','{3}'", clientNo, date, name, status);
            return GetData(url);
        }

        public static DataSet GetMenuSetToiletList(string clientNo)//菜单 作业公厕设定-公厕作业人员查询
        {
            //exec GetWorkerToiletInfo '0000004'
            String url = string.Format("exec GetWorkerToiletInfo '{0}'", clientNo);
            return GetData(url);
        }

        public static DataSet GetMenuSetCleanerList(string clientNo)//菜单 作业公厕设定-人员作业公厕设置
        {
            //exec GetWorkerToiletRelation '0000004'
            String url = string.Format("exec GetWorkerToiletRelation '{0}'", clientNo);
            return GetData(url);
        }

        public static DataSet GetMenuSetCleanerListDoubleClick(string cleanerName)//菜单 作业公厕设定-人员作业公厕设置 双击
        {
            //exec GetWorkerToiletRelation '0000004'
            String url = string.Format("SELECT a.[VID],a.[ID],b.NAME,[BEGINTIME],[ENDTIME],[CREATE_DATE],d.LEVEL,a.WORKCOUNT FROM [dbo].[WORKER_TOILET_RELATION] a, [dbo].[PLACE_TOILET] b, [dbo].[WORKER_TOILET] c,[dbo].[PLACE_TOILETINFO] d where a.ID=b.ID and a.VID =c.VID and c.NAME= '{0}' and a.ID=d.ID", cleanerName);
            return GetData(url);
        }

        public static DataSet GetMenuCleanerList(string clientNo, string name)//菜单 人员管理列表
        {
            //Select * from WORKER_TOILET where LEFT(CLIENT_NO,LEN('0000004'))=0000004 and NAME like '%兰%'
            String url = string.Format("Select * from WORKER_TOILET where LEFT(CLIENT_NO,LEN('{0}'))={1}  and NAME like '%{2}%'", clientNo, clientNo, name);
            return GetData(url);
        }
        
        public static DataSet GetMenuCleanerTeam(string clientNo)//菜单 人员组织机构
        {
            //Select * from CLIENT_WORKER_TOILET where LEFT(CLIENT_NO,LEN('0000004'))=0000004 and LEN(CLIENT_NO) =9
            String url = string.Format("Select * from [CLIENT_WORKER_TOILET] where LEFT(CLIENT_NO,LEN('{0}'))={1} and LEN(CLIENT_NO) =9", clientNo, clientNo);
            return GetData(url);
        }

        public static DataSet GetToiletInfo(string clientNo)//实时在线统计 公厕实时监控统计
        {
            String url = string.Format("Exec R_GetToiletInfo '{0}'", clientNo);
            return GetData(url);
        }

        public static DataSet GetData(string sql)
        {
            SqlConnection cn = new SqlConnection(Services.ConnectionString);
            SqlCommand cm = new SqlCommand(sql, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
    }
}